package com.gjy.sql.oracle.advanced;

import com.gjy.sql.oracle.OracleUtil;
import com.google.common.collect.Lists;
import oracle.jdbc.OracleTypes;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.List;

/**
 * @author gjy
 * @version 1.0
 * @since 2025-06-30 09:42:43
 */
public class CursorAdv {

    /**
     * -- 游标配合Java查询
     * create or replace package summary_pkg as
     * TYPE summary_cursor_type is ref cursor;
     * procedure get_summary(ap in varchar2, summary_cursor out summary_cursor_type);
     * end summary_pkg;
     * /
     * create or replace package body summary_pkg as
     * procedure get_summary(ap in varchar2, summary_cursor out summary_cursor_type) is
     * begin
     * open summary_cursor for
     * select CUST_ID, CUST_TYPE, ACCOUNT_PERIOD, COST_CODE
     * from INCOME_SUMMARY_SETTLE_120_25
     * where ACCOUNT_PERIOD = ap;
     * end get_summary;
     * end summary_pkg;
     * /
     * 查询摘要信息
     * 该方法通过调用Oracle数据库的存储过程获取摘要信息，并将结果作为字符串列表返回
     * 主要用于需要查询和处理大量摘要数据的场景
     *
     * @param ap 账单周期，用于指定查询的账单周期
     * @return 包含摘要信息的字符串列表，每个字符串包含客户ID、客户类型、账单周期和费用代码
     */
    public List<String> querySummaryCursor(String ap) {
        List<String> list = Lists.newArrayList();

        try (Connection connection = OracleUtil.connection();
             CallableStatement cs = connection.prepareCall("{call summary_pkg.get_summary(?,?)}")) {
            cs.setString(1, ap);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            // 必须调大，默认每10行和数据库交互一次，效率极低
            cs.setFetchSize(10000);
            cs.execute();
            try (ResultSet rs = (ResultSet) cs.getObject(2)) {
                while (rs.next()) {
                    String custId = rs.getString("CUST_ID");
                    String custType = rs.getString("CUST_TYPE");
                    String costCode = rs.getString("COST_CODE");
                    String accountPeriod = rs.getString("ACCOUNT_PERIOD");
                    list.add(custId + "_" + custType + "_" + accountPeriod + "_" + costCode);
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return list;
    }

}
